const mysql = require('mysql2');
const dbconfig = require('./database');

const pool = mysql.createPool({
  ...dbconfig.mysql,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
});

module.exports = {
  query: function (sql, params = []) {
    return new Promise((resolve, reject) => {
      pool.getConnection(function (err, connection) {
        if (err) {
          console.error('数据库连接失败:', err);
          reject(err);
          return;
        }

        connection.query(sql, params, function (err, rows, fields) {
          connection.release();

          if (err) {
            console.error('SQL 执行失败:', err);
            console.error('SQL:', sql);
            console.error('参数:', params);
            reject(err);
          } else {
            resolve(rows);
          }
        });
      });
    });
  },

  transaction: function (transactionFn) {
    return new Promise((resolve, reject) => {
      pool.getConnection(function (err, connection) {
        if (err) {
          reject(err);
          return;
        }

        connection.beginTransaction(function (err) {
          if (err) {
            connection.release();
            reject(err);
            return;
          }

          Promise.resolve(transactionFn(connection))
            .then(result => {
              connection.commit(function (err) {
                if (err) {
                  return connection.rollback(function () {
                    connection.release();
                    reject(err);
                  });
                }
                connection.release();
                resolve(result);
              });
            })
            .catch(error => {
              connection.rollback(function () {
                connection.release();
                reject(error);
              });
            });
        });
      });
    });
  },

  promise: {
    query: function (sql, params = []) {
      return pool.promise().query(sql, params)
        .then(([rows, fields]) => rows)
        .catch(err => {
          console.error('SQL 执行失败:', err);
          console.error('SQL:', sql);
          console.error('参数:', params);
          throw err;
        });
    },

    transaction: async function (transactionFn) {
      const connection = await pool.promise().getConnection();

      try {
        await connection.beginTransaction();
        const result = await transactionFn(connection);
        await connection.commit();
        return result;
      } catch (error) {
        await connection.rollback();
        throw error;
      } finally {
        connection.release();
      }
    }
  },

  close: function () {
    return new Promise((resolve, reject) => {
      pool.end(function (err) {
        if (err) {
          reject(err);
        } else {
          console.log('数据库连接池已关闭');
          resolve();
        }
      });
    });
  }
};